package CelcatCSVGenerator;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


public class CSVGenerator {
	private FileWriter m_fw;
	
	private static final String CT_EVENT_FILE_HEADER = "CT_EVENT\r\n_event_id,_day_of_week,_start_time,_end_time,_weeks,_break_mins,_spanName,_event_catName,_tag1,_tag2,_capacity_req,_deptName,_global_event,_protected,_suspended,_grouping_id,_registers_req,_lock,_resType,_resName,_resWeeks,_layoutName,_scheduling_status,_quantity\r\n";
	
	public void createCSVFile(Connection p_ConnectionDB, String p_fileOut) throws SQLException, IOException{
		ResultSet res;
		Statement statement;
		
		//Pour executer une commande
		statement =  p_ConnectionDB.createStatement();
		
		//Execution de la requete
		res = statement.executeQuery("SELECT * FROM CT_EVENT;");
		
		//Creation du fichier en ecriture si il existe on l'ecrase
		m_fw = new FileWriter(p_fileOut, false);
		
		//Ecriture du header du fichier CT_EVENT
		m_fw.write(CT_EVENT_FILE_HEADER);
		
		//On parcourt toute les donn�es de la table
		while(res.next()){
			String new_data;
			//On obtient les attributs
			int event_id 		= res.getInt("_event_id");
			String day_of_week	= res.getString("_day_of_week");
			int start_time		= res.getInt("_start_time");
			int end_time		= res.getInt("_end_time");
			String weeks		= res.getString("_weeks");
			int break_mins		= res.getInt("_break_mins");
			String spanName		= res.getString("_spanName");
			String event_catName= res.getString("_event_catName");
			String tag1			= res.getString("_tag1");
			String tag2			= res.getString("_tag2");
			int capacity_req	= res.getInt("_capacity_req");
			String deptName		= res.getString("_deptName");
			char global_event	= (char)res.getByte("_global_event");
			char protected_		= (char)res.getByte("_protected");
			char suspended		= (char)res.getByte("_suspended");
			String grouping_id	= res.getString("_grouping_id");
			String register_req = res.getString("registers_req");
			String lock			= res.getString("_lock");
						
			List<Resource> resources = getResources(p_ConnectionDB, event_id);
						
			//Ecriture des donn�es dans le fichier CSV
			m_fw.write(event_id+",");
			m_fw.write(day_of_week+",");
			new_data = String.format("%02d:%02d,%02d:%02d,", start_time/60, start_time%60, end_time/60, end_time%60);
			m_fw.write(new_data);
			m_fw.write(weeks+",");	//TODO : a voir le string me parait bizarre
			m_fw.write(break_mins+",");
			m_fw.write(spanName+",");
			m_fw.write(event_catName+",");
			m_fw.write(tag1+",");
			m_fw.write(tag2+",");
			m_fw.write(capacity_req+",");
			m_fw.write(deptName+",");
			m_fw.write(global_event+",");
			m_fw.write(protected_+",");
			m_fw.write(suspended+",");
			m_fw.write(grouping_id+",");
			m_fw.write(register_req+",");
			m_fw.write(lock+",,,,,,\r\n");
			
			//Mise des resources
			while(resources.size()>0){
				m_fw.write(",,,,,,,,,,,,,,,,,,");
				m_fw.write(resources.get(0).getType()+",");
				m_fw.write(resources.get(0).getName()+",");
				m_fw.write(resources.get(0).getWeeks()+",");
				m_fw.write(resources.get(0).getLayoutName()+",");
				m_fw.write(resources.get(0).getScheduling()+",");
				m_fw.write(resources.get(0).getQuantity()+"\r\n");
				resources.remove(0);
			}
		}
		
		//Fermeture du fichier
		m_fw.close();
	}
	
	public void closeFile() throws IOException{
		m_fw.close();
	}
	
	private List<Resource> getResources(Connection  p_ConnectionDB, int eventID) throws SQLException{
		List<Resource> resources = new ArrayList<Resource>();
		ResultSet res;
		Statement statement;
		
		//Creation de la connexion
		statement = p_ConnectionDB.createStatement();
		
		res = statement.executeQuery(
				"SELECT _module_id, _layoutName, _scheduling_status, _quantity, _resWeeks " +
				"FROM CT_EVENT_MODULE " +
				"WHERE CT_EVENT_MODULE._event_id="+eventID+";");

		while(res.next()){
			int module_id = res.getInt("_module_id");
			String layoutName = res.getString("_layoutName");
			char scheduling = (char) res.getByte("_scheduling_status");
			int quantity = res.getInt("_scheduling_status");
			String weeks = res.getString("_resWeeks"); //TODO : surement un truc a modifier
			
			ResultSet res2 = statement.executeQuery("SELECT _unique_name FROM CT_MODULE WHERE CT_MODULE._module_id="+module_id+";");
			 
			
			while(res2.next())
			{
				String name = res2.getString("_unique_name");
				
				
				resources.add(new Resource("Module", name, weeks, layoutName, scheduling, quantity));
			}
		}
		
		res = statement.executeQuery(
				"SELECT _group_id, _layoutName, _scheduling_status, _quantity, _resWeeks " +
				"FROM CT_EVENT_GROUP " +
				"WHERE CT_EVENT_GROUP._event_id="+eventID+";");
		
		while(res.next()){
			int group_id = res.getInt("_group_id");
			String layoutName = res.getString("_layoutName");
			char scheduling = (char) res.getByte("_scheduling_status");
			int quantity = res.getInt("_scheduling_status");
			String weeks = res.getString("_resWeeks"); //TODO : surement un truc a modifier
			
			ResultSet res2 = statement.executeQuery("SELECT _unique_name FROM CT_GROUP WHERE CT_GROUP._group_id="+group_id+";");
			 
			
			while(res2.next())
			{
				String name = res2.getString("_unique_name");
								
				resources.add(new Resource("Group", name, weeks, layoutName, scheduling, quantity));
			}
		}
		
		res = statement.executeQuery(
				"SELECT _room_id, _layoutName, _scheduling_status, _quantity, _resWeeks " +
				"FROM CT_EVENT_ROOM " +
				"WHERE CT_EVENT_ROOM._event_id="+eventID+";");
		
		while(res.next()){
			int room_id = res.getInt("_room_id");
			String layoutName = res.getString("_layoutName");
			char scheduling = (char) res.getByte("_scheduling_status");
			int quantity = res.getInt("_scheduling_status");
			String weeks = res.getString("_resWeeks"); //TODO : surement un truc a modifier
			
			ResultSet res2 = statement.executeQuery("SELECT _unique_name FROM CT_ROOM WHERE CT_ROOM._room_id="+room_id+";");
			 
			
			while(res2.next())
			{
				String name = res2.getString("_unique_name");
								
				resources.add(new Resource("Room", name, weeks, layoutName, scheduling, quantity));
			}
		}
		
		return resources;
	}
}
